import io

from pyhive import hive
import pandas as pd
from wordcloud import WordCloud
import matplotlib.pyplot as plt
from mpld3 import save_html
from matplotlib.backends.backend_svg import FigureCanvasSVG
from matplotlib.font_manager import FontManager
from datetime import datetime, date

# 配置Hive连接参数
hive_host = 'localhost'
hive_port = '10000'
hive_user = 'edy'
hive_db = 'default'
hive_password = '1497zs'
hive_table = 'hot_baidu_session_word_count'  # 表名
text_column = 'word'  # 文本字段名
count_day = (datetime.today() - datetime.strptime("2024-05-23", "%Y-%m-%d")).days


# 连接Hive数据库，并从指定表中获取数据
def fetch_data_from_hive():
    """
    连接Hive并获取数据。
    返回:
        data: 从Hive查询得到的数据。
    """
    try:
        conn = hive.Connection(host=hive_host, port=hive_port, username=hive_user, database=hive_db)
        cursor = conn.cursor()

        query = (f"select /* + mapjoin(t1) */ t2.* from black_word t1 right join (SELECT "
                 f"{text_column},sum(count_word) as count_words "
                 f"FROM {hive_table} "
                 f" group by {text_column}) t2 on t1.balck_word=t2.{text_column} where t1.balck_word is null")
        print(query)
        cursor.execute(query)
        data = cursor.fetchall()

        cursor.close()
        conn.close()
        return data
    except Exception as e:
        print(f"Error connecting to Hive: {e}")
        return None


# 根据从Hive获取的数据生成词云，并将词云保存为HTML文件
def generate_wordcloud_to_html(data):
    """
    根据输入数据生成词云图，并保存为HTML和SVG格式。

    参数:
        data: 从Hive获取到的数据。
    """
    if data is not None:
        freq_dict = pd.DataFrame(data, columns=[text_column, "count_words"]).set_index(text_column)[
            'count_words'].to_dict()
        wordcloud = WordCloud(width=800, height=400, max_words=100, background_color='white',
                              font_path="/System/Library/Fonts/STHeiti Light.ttc").generate_from_frequencies(freq_dict)

        fig = plt.figure(figsize=(10, 5))
        # plt.rcParams["font.sans-serif"] = ["SimSong"]  # 设置字体
        FigureCanvasSVG(fig)  # 使用SVG后端
        plt.imshow(wordcloud, interpolation='bilinear')
        plt.axis('off')
        plt.title(f'Word Cloud from {hive_table} on {count_day} days')

        # 保存词云图作为SVG文件
        with open('/Users/edy/Downloads/PY_TEST/pysparktest/sources/wordcloud.png', 'wb') as f:
            fig.savefig(f, format='png', bbox_inches='tight')
            print("Word cloud saved as 'wordcloud.png'")

        # # 生成包含词云图的HTML文件
        # with open('/Users/edy/Downloads/PY_TEST/pysparktest/sources/wordcloud.png', 'r') as svg_file:
        #     svg_content = svg_file.read()
        # html_string = '<div>' + svg_content + '</div>'
        #
        # with open('/Users/edy/Downloads/PY_TEST/pysparktest/sources/wordcloud.html', 'w') as f:
        #     f.write(html_string)
        #     print("Word cloud also embedded in 'wordcloud.html'")
    else:
        print("No data fetched from Hive.")


if __name__ == "__main__":
    # fm = FontManager()
    # fonts = set([f.name for f in fm.ttflist])
    # print(sorted(fonts))
    hive_data = fetch_data_from_hive()
    generate_wordcloud_to_html(hive_data)
